package org.lgc.wordsplit;

import java.io.File;
import java.io.IOException;

 
 




import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;

//import com.sun.media.sound.InvalidFormatException;
//import com.sun.rowset.internal.Row;

/**
/* 版权所有： 广州敏道科技有限公司
/*
/* 功能描述：
/*
/* 创 建 人：李国才
/* 创建时间：2016年6月4日 下午9:31:51 	
 **/
public class ExcelMergeRegionTest {
	public static void main(String[] args) throws org.apache.poi.openxml4j.exceptions.InvalidFormatException {
		ExcelMergeRegionTest t=new ExcelMergeRegionTest();
		t.readExcelToObj("f:\\usr\\vpatient_demo.xlsx");  
	}
    
//    public void testReadExcel() {  
//        readExcelToObj("C:\\Users\\Administrator\\Desktop\\11.xls");  
//    }  
      
    /**  
    * 读取excel数据  
    * @param  path  
     * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException 
    */  
    private void readExcelToObj(String path) throws org.apache.poi.openxml4j.exceptions.InvalidFormatException {  
      
        Workbook wb = null;  
        try {  
            wb = WorkbookFactory.create(new File(path));  
            readExcel(wb, 0, 0, 0);  
        } catch (InvalidFormatException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
    }  
      
    /**  
    * 读取excel文件  
    * @param  wb   
    * @param sheetIndex sheet页下标：从0开始  
    * @param startReadLine 开始读取的行:从0开始  
    * @param tailLine 去除最后读取的行  
    */  
    private void readExcel(Workbook wb,int sheetIndex, int startReadLine, int tailLine) {  
        
        Sheet sheet = wb.getSheetAt(sheetIndex);  
        Row row = null;  
          
        for(int i=startReadLine; i<sheet.getLastRowNum()-tailLine+1; i++) {  
            
            row = sheet.getRow(i); 
            for(Cell c : row) {  
                c.setCellType(Cell.CELL_TYPE_STRING);
                boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());  
                //判断是否具有合并单元格  
                if(isMerge) {  
                    String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());  
                    System.out.print(rs + "  ");  
                }else {  
                    System.out.print(c.getRichStringCellValue()+"  ");  
                }  
            }  
            System.out.println();  
              
        }  
      
    }  
      
    /**   
    * 获取合并单元格的值   
    * @param sheet   
    * @param row   
    * @param column   
    * @return   
    */    
    public String getMergedRegionValue(Sheet sheet ,int row , int column){    
        
        int sheetMergeCount = sheet.getNumMergedRegions();    
            
        for(int i = 0 ; i < sheetMergeCount ; i++){    
            CellRangeAddress ca = sheet.getMergedRegion(i);    
            int firstColumn = ca.getFirstColumn();    
            int lastColumn = ca.getLastColumn();    
            int firstRow = ca.getFirstRow();    
            int lastRow = ca.getLastRow();    
                
            if(row >= firstRow && row <= lastRow){    
                    
                if(column >= firstColumn && column <= lastColumn){    
                    Row fRow = sheet.getRow(firstRow);    
                    Cell fCell = fRow.getCell(firstColumn);    
                    return getCellValue(fCell) ;    
                }    
            }    
        }    
            
        return null ;    
    }    
      
    /**  
    * 判断合并了行  
    * @param sheet  
    * @param row  
    * @param column  
    * @return  
    */  
    private boolean isMergedRow(Sheet sheet,int row ,int column) {  
      
      int sheetMergeCount = sheet.getNumMergedRegions();  
      for (int i = 0; i < sheetMergeCount; i++) {  
        CellRangeAddress range = sheet.getMergedRegion(i);  
        int firstColumn = range.getFirstColumn();  
        int lastColumn = range.getLastColumn();  
        int firstRow = range.getFirstRow();  
        int lastRow = range.getLastRow();  
        if(row == firstRow && row == lastRow){  
            if(column >= firstColumn && column <= lastColumn){  
                return true;  
            }  
        }  
      }  
      return false;  
    }  
      
    /**  
    * 判断指定的单元格是否是合并单元格  
    * @param sheet   
    * @param row 行下标  
    * @param column 列下标  
    * @return  
    */  
    private boolean isMergedRegion(Sheet sheet,int row ,int column) {  
      
      int sheetMergeCount = sheet.getNumMergedRegions();  
      for (int i = 0; i < sheetMergeCount; i++) {  
        
        CellRangeAddress range = sheet.getMergedRegion(i);  
        int firstColumn = range.getFirstColumn();  
        int lastColumn = range.getLastColumn();  
        int firstRow = range.getFirstRow();  
        int lastRow = range.getLastRow();  
        if(row >= firstRow && row <= lastRow){  
            if(column >= firstColumn && column <= lastColumn){  
                return true;  
            }  
        }  
      }  
      return false;  
    }  
      
    /**  
    * 判断sheet页中是否含有合并单元格   
    * @param sheet   
    * @return  
    */  
    private boolean hasMerged(Sheet sheet) {  
            return sheet.getNumMergedRegions() > 0 ? true : false;  
    }  
      
    /**  
    * 合并单元格  
    * @param sheet   
    * @param firstRow 开始行  
    * @param lastRow 结束行  
    * @param firstCol 开始列  
    * @param lastCol 结束列  
    */  
    private void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {  
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));  
    }  
      
    /**   
    * 获取单元格的值   
    * @param cell   
    * @return   
    */    
    public String getCellValue(Cell cell){    
            
        if(cell == null) return "";    
            
        if(cell.getCellType() == Cell.CELL_TYPE_STRING){    
                
            return cell.getStringCellValue();    
                
        }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){    
                
            return String.valueOf(cell.getBooleanCellValue());    
                
        }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){    
                
            return cell.getCellFormula() ;    
                
        }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){    
                
            return String.valueOf(cell.getNumericCellValue());    
                
        }    
        return "";    
    }    
}
